﻿using DAL;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using Model;
using System.Data.SqlClient;

namespace DLL.Teacher操作类DAL
{
    public class QRcodeDAL
    {
        private SQLHelper sqlhelper = null;
        public QRcodeDAL()
        {
            sqlhelper = new SQLHelper();
        }


        #region 根据学号更新对应的验证码-刘檬-2018年12月19日20:34:57
        /// <summary>
        /// 将验证码与学号更新到数据库
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public Boolean UpdateStudentInfo(StudentInfoEntity studentinfo)
        {
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@studentID",studentinfo.studentID),
                new SqlParameter("@code",studentinfo.code)                              
            };
            string sql = "update StudentinfoEntity set code=@code where studentID=@studentID ";
            int res = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
            bool flag = false;
            if (res > 0)
            {
                flag = true;
            }
            return flag;
        }
        #endregion

        #region 查询用户学号及验证码等信息-刘檬-2018年12月19日20:34:44----白爱民修改：条件添加不为空--2019年1月6日15:56:30
        /// <summary>
        /// 查询用户学号及验证码等信息
        /// </summary>
        /// <returns></returns>
        public DataTable getStudentInfo()
        {
            string sql = "Select studentID,examPlaceID,studentName,grade,code  from StudentinfoEntity  where code is not null AND code !=''";
            DataTable dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion

        #region 查询未推送数据--白爱民--2019年1月4日16:18:26
        /// <summary>
        /// 查询未推送数据
        /// </summary>
        /// <returns></returns>
        public DataTable SelectScoreAdornDisplayData()
        {
            string sql = "select userId,userName, totalScore,loginTime,is_Input from ScoreAdornEntity where Is_Input='1'";
            DataTable dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }

        #endregion

        #region 根据学号查询用户验证码等信息-刘檬-2018年12月20日13:02:21--白爱民删除了
        /// <summary>
        /// 根据学号查询用户验证码等信息
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectStudentByID(StudentInfoEntity studentinfo)
        {
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@studentID",studentinfo.studentID)
            };
            string sql = "Select studentID,examPlaceID,studentName,grade,code  from StudentinfoEntity  where studentID=@studentID";
            DataTable dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 查询目前考场正在考试人数-刘檬-2018年12月28日18:07:07——修改人：刘檬-SQL语句修改-2018年12月30日14:27:43
        /// <summary>
        /// 查询目前考场正在考试人数
        /// </summary>
        /// <param name="StudentBindPaperType"></param>
        /// <returns></returns>
        public DataTable SelectByIP(StudentBindPaperTypeEntity StudentBindPaperType)
        {
            SqlParameter[] paras = new SqlParameter[] {
                                                        new SqlParameter("@ip","%" + StudentBindPaperType.IP + "%")
                                                     };
            string sql = "SELECT count(*) from StudentBindPaperTypeEntity where IsUse ='1' and DateDiff(day,TimeTamp,getdate())=0 and IP like @ip";
            DataTable dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion;

        #region 查询每个学生总分排名--白爱民2019年1月4日10:32:27
        /// <summary>
        /// 每个学生总分排名
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectScoreAdornOrder(ScoreAdornEntity scoreAdornEntity)
        {
            //获取数据库当前时间
            //查询时间格式为yyyy-MM-dd
            string sqltime = "Select convert(varchar(10),getdate(),120)";
            DataTable table = sqlhelper.ExecuteQuery(sqltime, CommandType.Text);
            string date = table.Rows[0][0].ToString();

            
            DataTable dt = new DataTable();
            string sql = "select * from ScoreAdornEntity WHERE totalScore>@totalScore and  datediff(day,loginTime,@date)=0";  //此代码用于win7台式机

            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@totalScore",scoreAdornEntity.totalScore),
            new SqlParameter("@date",date)
            //new SqlParameter("@is_Input","1")
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 查询当天人次练习人数--白爱民--2019年1月4日14:55:50
        /// <summary>
        /// 查询当天人次练习人数
        /// </summary>
        /// <returns></returns>
        public DataTable SelectScoreAdornAllday()
        {
            string sql = "select count(*) from ScoreAdornEntity where datediff(day,loginTime,getdate())=0";
            DataTable  dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion
    }
}